 |
|
|
 |
| Subject: Closing an Edited Email Using Excel VBA |
 |
 |
 |
| Product Area: Notes Single Login |
 |
| Technical Area: Application Development |
 |
| Platform: Windows |
 |
| Release: 8.5 |
 |
| Reproducible: Always |
 |
 |
 |
 |
Hey there,
I created an Excel-based reporting system, which uses individual files in a shared folder. The users enter their updates in their files, and then use macros in the file to
1)request approval for their updates from their managers, and
2)release the approved data for consolidation.
The issue I am encountering has to do with item 1. The macro to request approval opens up a Lotus Notes e-mail, populates it with the appropriate contacts, subject, and body, and then copies the relevant cells in Excel and pastes them as a picture into the Lotus Notes email (allowing the approver to view the update on their computer or BlackBerry).
The macro has worked very well in the past, but we've had one periodically reoccurring issue. From time to time when the macro is run (generally the first time the macro has been used in a while), the email will generate appropriately but the picture of the excel cells will not appear. When this happens if the user closes the email, and reruns the macro everything works properly.
We've requested that our users use this work around, but ideally I'd like to fix this in a better way. From my experience, the code to copy and paste the Excel cells in Lotus Notes always works the second time, so what I want to do is create code that would create the email like it usually does close this first e-mail recreate the email.
So far I have been able to open and close a blank email, but I have not been able to close an email that has been edited (adding in the recipient names, subject, body, etc.). When I try to close the edited email, Lotus Notes opens a window asking "Do you want to send, save, or discard your changes? Choose Cancel to continue editing". I tried using SendKeys to send "D" for discard, but I just get a little note saying that Numlock has turned off.
Anyone have any suggestions? I'm including my code below.
Thank you!
Sub EmailforApproval()
'Opens approval email and copies and pastes relevant data into e-mail without sending
Application.ScreenUpdating = False
ActiveWorkbook.Save
Sheets("Reference").Visible = True
Dim UserName As String, MailDbName As String, ccRecipient As String, attachment1 As String, sendRecipient As String
Dim Maildb As Object, MailDoc As Object, AttachME As Object, Session As Object
Dim EmbedObj1 As Object, workspace As Object, NUIdoc As Object, NUIWorkSpace As Object
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = _
Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
sendRecipient = Sheets("Reference").Range("D6").Value
ccRecipient = Sheets("Reference").Range("D7").Value
MailDoc.Sendto = sendRecipient
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = Sheets("Reference").Range("d8").Value
MailDoc.body = vbNewLine & vbNewLine & "This data is ready for you to approve" & vbNewLine & vbNewLine & _
"Please press 'reply all' and indicate whether or not you approve these figures. Thank you"
MailDoc.Save True, False
Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)
With NUIdoc
.GotoField ("Body")
Sheets("Input Sheet").Range("b4:l5").Copy
.Paste
Application.CutCopyMode = False
End With
With NUIdoc
.Close
End With
' AppActivate "Lotus Notes"
SendKeys "d", True
'SendKeys "[TAB]", True
'SendKeys "[TAB]", True
'SendKeys "[Enter]", True
Set NUIdoc = NUIWorkSpace.EDITDocument(True, MailDoc)
With NUIdoc
.GotoField ("Body")
Sheets("Input Sheet").Range("b4:L5").Copy
.Paste
Application.CutCopyMode = False
End With
MailDoc.SaveMessageOnSend = True
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
.ScreenUpdating = True
.DisplayAlerts = True
End With
Sheets("Reference").Visible = False
Application.ScreenUpdating = True
AppActivate ("Notes")
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
End Sub
 
Feedback number WEBB8WGNJW created by ~Hal Zennuli on 07/23/2012
Status: Open
Comments:
|
| |
|
Return to top
Printer-friendly
